﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Windows.Forms;
using System.Xml;
using QAToolsAddin.GenericDialogs;
using QAToolsAddin.GenericDialogs.Fields;
using Excel = Microsoft.Office.Interop.Excel;
using System.Text.RegularExpressions;

namespace QAToolsAddin.UI {
    class Currency {

        private readonly Excel.Application _excelapp;
        private Excel.Range _range;
        private CultureInfo _culture;
        private Dictionary<string, CurrencyItem> _currenciesList;

        public Currency(Excel.Application application, CultureInfo culture) {
            try {
                _excelapp = application;
                _culture = culture;
                _range = application.ActiveWindow.RangeSelection;
                _currenciesList = new Dictionary<string, CurrencyItem>(){
                    #region Currencies
                    { "AED", new CurrencyItem("AED", "U.A.E.", "د.إ.‏", 0x3801, '.', 2 ) },
                    { "AFN", new CurrencyItem("AFN", "Afghanistan", "؋", 0x463, '.', 0 ) },
                    { "ALL", new CurrencyItem("ALL", "Albania", "Lek", 0x41C, ',', 1 ) },
                    { "AMD", new CurrencyItem("AMD", "Armenia", "դր.", 0x42B, '.', 3 ) },
                    { "ARS", new CurrencyItem("ARS", "Argentina", "$", 0x2C0A, ',', 2 ) },
                    { "AUD", new CurrencyItem("AUD", "Australia", "$", 0xC09, '.', 0 ) },
                    { "AZN", new CurrencyItem("AZN", "Azerbaijan", "ман.", 0x42C, ',', 3 ) },
                    { "BAM", new CurrencyItem("BAM", "Bosnia and Herzegovina", "КМ", 0x101A, ',', 3 ) },
                    { "BDT", new CurrencyItem("BDT", "Bangladesh", "৳", 0x845, '.', 2 ) },
                    { "BGN", new CurrencyItem("BGN", "Bulgaria", "лв.", 0x402, ',', 3 ) },
                    { "BHD", new CurrencyItem("BHD", "Bahrain", "د.ب.‏", 0x3C01, '.', 2 ) },
                    { "BND", new CurrencyItem("BND", "Brunei Darussalam", "$", 0x83E, ',', 0 ) },
                    { "BOB", new CurrencyItem("BOB", "Bolivia", "$b", 0x46B, ',', 2 ) },
                    { "BRL", new CurrencyItem("BRL", "Brazil", "R$", 0x416, ',', 2 ) },
                    { "BYR", new CurrencyItem("BYR", "Belarus", "р.", 0x423, ',', 3 ) },
                    { "BZD", new CurrencyItem("BZD", "Belize", "BZ$", 0x2809, '.', 0 ) },
                    { "CAD", new CurrencyItem("CAD", "Canada", "$", 0x45D, '.', 0 ) },
                    { "CHF", new CurrencyItem("CHF", "Switzerland", "CHF", 0x417, '.', 2 ) },
                    { "CLP", new CurrencyItem("CLP", "Chile", "$", 0x47A, ',', 2 ) },
                    { "CNY", new CurrencyItem("CNY", "People's Republic of China", "¥", 0x451, '.', 0 ) },
                    { "COP", new CurrencyItem("COP", "Colombia", "$", 0x240A, ',', 2 ) },
                    { "CRC", new CurrencyItem("CRC", "Costa Rica", "₡", 0x140A, ',', 0 ) },
                    { "CSD", new CurrencyItem("CSD", "Serbia and Montenegro (Former)", "Дин.", 0x81A, ',', 3 ) },
                    { "CZK", new CurrencyItem("CZK", "Czech Republic", "Kč", 0x405, ',', 3 ) },
                    { "DKK", new CurrencyItem("DKK", "Denmark", "kr.", 0x406, ',', 2 ) },
                    { "DOP", new CurrencyItem("DOP", "Dominican Republic", "RD$", 0x1C0A, '.', 0 ) },
                    { "DZD", new CurrencyItem("DZD", "Algeria", "د.ج.‏", 0x85F, '.', 2 ) },
                    { "EEK", new CurrencyItem("EEK", "Estonia", "kr", 0x425, '.', 3 ) },
                    { "EGP", new CurrencyItem("EGP", "Egypt", "ج.م.‏", 0xC01, '.', 2 ) },
                    { "ETB", new CurrencyItem("ETB", "Ethiopia", "ETB", 0x45E, '.', 0 ) },
                    { "EUR", new CurrencyItem("EUR", "Europe", "€", 1, ',', 3 ) },
                    { "GBP", new CurrencyItem("GBP", "United Kingdom", "£", 0x809, '.', 0 ) },
                    { "GEL", new CurrencyItem("GEL", "Georgia", "Lari", 0x437, ',', 3 ) },
                    { "GTQ", new CurrencyItem("GTQ", "Guatemala", "Q", 0x486, '.', 0 ) },
                    { "HKD", new CurrencyItem("HKD", "Hong Kong S.A.R.", "HK$", 0xC04, '.', 0 ) },
                    { "HNL", new CurrencyItem("HNL", "Honduras", "L.", 0x480A, '.', 2 ) },
                    { "HRK", new CurrencyItem("HRK", "Croatia", "kn", 0x41A, ',', 3 ) },
                    { "HUF", new CurrencyItem("HUF", "Hungary", "Ft", 0x40E, ',', 3 ) },
                    { "IDR", new CurrencyItem("IDR", "Indonesia", "Rp", 0x421, ',', 0 ) },
                    { "ILS", new CurrencyItem("ILS", "Israel", "₪", 0x40D, '.', 2 ) },
                    { "INR", new CurrencyItem("INR", "India", "Rs.", 0x439, '.', 2 ) },
                    { "IQD", new CurrencyItem("IQD", "Iraq", "د.ع.‏", 0x801, '.', 2 ) },
                    { "IRR", new CurrencyItem("IRR", "Iran", "ريال", 0x429, '.', 2 ) },
                    { "ISK", new CurrencyItem("ISK", "Iceland", "kr.", 0x40F, ',', 3 ) },
                    { "JMD", new CurrencyItem("JMD", "Jamaica", "J$", 0x2009, '.', 0 ) },
                    { "JOD", new CurrencyItem("JOD", "Jordan", "د.ا.‏", 0x2C01, '.', 2 ) },
                    { "JPY", new CurrencyItem("JPY", "Japan", "¥", 0x411, '.', 0 ) },
                    { "KES", new CurrencyItem("KES", "Kenya", "S", 0x441, '.', 0 ) },
                    { "KGS", new CurrencyItem("KGS", "Kyrgyzstan", "сом", 0x440, '.', 3 ) },
                    { "KHR", new CurrencyItem("KHR", "Cambodia", "៛", 0x453, '.', 1 ) },
                    { "KRW", new CurrencyItem("KRW", "Korea", "₩", 0x412, '.', 0 ) },
                    { "KWD", new CurrencyItem("KWD", "Kuwait", "د.ك.‏", 0x3401, '.', 2 ) },
                    { "KZT", new CurrencyItem("KZT", "Kazakhstan", "Т", 0x43F, '.', 0 ) },
                    { "LAK", new CurrencyItem("LAK", "Lao P.D.R.", "₭", 0x454, '.', 1 ) },
                    { "LBP", new CurrencyItem("LBP", "Lebanon", "ل.ل.‏", 0x3001, '.', 2 ) },
                    { "LKR", new CurrencyItem("LKR", "Sri Lanka", "රු.", 0x45B, '.', 2 ) },
                    { "LTL", new CurrencyItem("LTL", "Lithuania", "Lt", 0x427, ',', 3 ) },
                    { "LVL", new CurrencyItem("LVL", "Latvia", "Ls", 0x426, ',', 2 ) },
                    { "LYD", new CurrencyItem("LYD", "Libya", "د.ل.‏", 0x1001, '.', 0 ) },
                    { "MAD", new CurrencyItem("MAD", "Morocco", "د.م.‏", 0x1801, '.', 2 ) },
                    { "MKD", new CurrencyItem("MKD", "Macedonia (FYROM)", "ден.", 0x42F, ',', 3 ) },
                    { "MNT", new CurrencyItem("MNT", "Mongolia", "₮", 0x450, ',', 1 ) },
                    { "MOP", new CurrencyItem("MOP", "Macao S.A.R.", "MOP", 0x1404, '.', 0 ) },
                    { "MVR", new CurrencyItem("MVR", "Maldives", "ރ.", 0x465, '.', 3 ) },
                    { "MXN", new CurrencyItem("MXN", "Mexico", "$", 0x80A, '.', 0 ) },
                    { "MYR", new CurrencyItem("MYR", "Malaysia", "RM", 0x43E, '.', 0 ) },
                    { "NIO", new CurrencyItem("NIO", "Nicaragua", "C$", 0x468, '.', 2 ) },
                    { "NOK", new CurrencyItem("NOK", "Norway", "kr", 0x414, ',', 2 ) },
                    { "NPR", new CurrencyItem("NPR", "Nepal", "रु", 0x461, '.', 0 ) },
                    { "NZD", new CurrencyItem("NZD", "New Zealand", "$", 0x481, '.', 0 ) },
                    { "OMR", new CurrencyItem("OMR", "Oman", "ر.ع.‏", 0x2001, '.', 2 ) },
                    { "PAB", new CurrencyItem("PAB", "Panama", "B/.", 0x180A, '.', 2 ) },
                    { "PEN", new CurrencyItem("PEN", "Peru", "S/.", 0xC6B, '.', 2 ) },
                    { "PHP", new CurrencyItem("PHP", "Republic of the Philippines", "Php", 0x464, '.', 0 ) },
                    { "PKR", new CurrencyItem("PKR", "Islamic Republic of Pakistan", "Rs", 0x420, '.', 0 ) },
                    { "PLN", new CurrencyItem("PLN", "Poland", "zł", 0x415, ',', 3 ) },
                    { "PYG", new CurrencyItem("PYG", "Paraguay", "Gs", 0x3C0A, ',', 2 ) },
                    { "QAR", new CurrencyItem("QAR", "Qatar", "ر.ق.‏", 0x4001, '.', 2 ) },
                    { "RON", new CurrencyItem("RON", "Romania", "lei", 0x418, ',', 3 ) },
                    { "RSD", new CurrencyItem("RSD", "Serbia", "Дин.", 0x241A, ',', 3 ) },
                    { "RUB", new CurrencyItem("RUB", "Russia", "руб", 0x419, ',', 1 ) },
                    { "RWF", new CurrencyItem("RWF", "Rwanda", "RWF", 0x487, ',', 2 ) },
                    { "SAR", new CurrencyItem("SAR", "Saudi Arabia", "ر.س.‏", 0x401, '.', 2 ) },
                    { "SEK", new CurrencyItem("SEK", "Sweden", "kr", 0x41D, ',', 3 ) },
                    { "SGD", new CurrencyItem("SGD", "Singapore", "$", 0x1004, '.', 0 ) },
                    { "SYP", new CurrencyItem("SYP", "Syria", "ل.س.‏", 0x45A, '.', 2 ) },
                    { "THB", new CurrencyItem("THB", "Thailand", "฿", 0x41E, '.', 0 ) },
                    { "TJS", new CurrencyItem("TJS", "Tajikistan", "т.р.", 0x428, '.', 3 ) },
                    { "TMT", new CurrencyItem("TMT", "Turkmenistan", "m.", 0x442, ',', 1 ) },
                    { "TND", new CurrencyItem("TND", "Tunisia", "د.ت.‏", 0x1C01, '.', 2 ) },
                    { "TRY", new CurrencyItem("TRY", "Turkey", "TL", 0x41F, ',', 3 ) },
                    { "TTD", new CurrencyItem("TTD", "Trinidad and Tobago", "TT$", 0x2C09, '.', 0 ) },
                    { "TWD", new CurrencyItem("TWD", "Taiwan", "NT$", 0x404, '.', 0 ) },
                    { "UAH", new CurrencyItem("UAH", "Ukraine", "₴", 0x422, ',', 1 ) },
                    { "USD", new CurrencyItem("USD", "United States", "$", 0x409, '.', 0 ) },
                    { "UYU", new CurrencyItem("UYU", "Uruguay", "$U", 0x380A, ',', 2 ) },
                    { "UZS", new CurrencyItem("UZS", "Uzbekistan", "сўм", 0x443, ',', 3 ) },
                    { "VEF", new CurrencyItem("VEF", "Bolivarian Republic of Venezuela", "Bs. F.", 0x200A, ',', 2 ) },
                    { "VND", new CurrencyItem("VND", "Vietnam", "₫", 0x42A, ',', 3 ) },
                    { "XOF", new CurrencyItem("XOF", "Senegal", "XOF", 0x488, ',', 3 ) },
                    { "YER", new CurrencyItem("YER", "Yemen", "ر.ي.‏", 0x2401, '.', 2 ) },
                    { "ZAR", new CurrencyItem("ZAR", "South Africa", "R", 0x432, ',', 2 ) },
                    { "ZWL", new CurrencyItem("ZWL", "Zimbabwe", "Z$", 0x3009, '.', 0 ) }
                    #endregion Currencies
                };
                _currenciesList["EUR"].Rate = 1;
            } catch (Exception ex) { new FrmException(_excelapp, ex).ShowDialog(); }
        }

        protected class CurrencyItem {
            public DateTime Time;
            public string Code { get; private set; }
            public string Name { get; private set; }
            public string Symbole { get; private set; }
            public int LCID { get; private set; }
            public char DecimalCharacter { get; private set; }
            public int Position { get; private set; }
            public double Rate { get; set; }

            public CurrencyItem() { }

            public CurrencyItem(string code, string name, string symbole, int lcid, char decimalCharacter, int position) {
                Code = code;
                Name = name;
                Symbole = symbole;
                LCID = lcid;
                DecimalCharacter = decimalCharacter;
                Position = position;
            }

            public string Format {
                get { return Utils.GetCurrencyFormat(LCID, Symbole, Position); }
            }

            public override string ToString() {
                if (Code == string.Empty)
                    return string.Empty;
                return Code + "  " + Name + "  " + Symbole + "   " + ToDateTimeString();
            }

            public string ToDateTimeString() {
                return (Time - DateTime.UtcNow).TotalHours > 24 ?
                    Time.ToLocalTime().ToString()
                    : Time.ToLocalTime().ToShortTimeString();
            }

        }

        public void ToCurrency() {
            //var xmldoc = LoadXmlFile(@"http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml");
            var xmldoc = LoadXmlFile(@"http://finance.yahoo.com/webservice/v1/symbols/allcurrencies/quote");
            if (xmldoc == null) return;
            var currencies = ParseCurrencies(xmldoc);
            //Try to figure out the first cell currency
            int indexFrom = GetCellCurrencyIndex((Excel.Range)_range.Cells[1, 1], currencies);

            var f0 = new FieldList<CurrencyItem>("From currency", "From currency", currencies, indexFrom, true);
            var f1 = new FieldList<CurrencyItem>("To currency", "To currency", currencies, -1, true);
            var f2 = new FieldNumber("Rate", "Conversion rate", 0);
            var f3 = new FieldNumber("Rounding", "Number of digit after the decimal point (empty=disabled)", (double?)null, 0, 15, true);
            var f4 = new FieldCheckBox("Add symbol", "Add the metric symbol", true, true);

            EventHandler indexChanged = (sender, e) => {
                if (f0.Value == null || f1.Value == null) return;
                f2.Value = Math.Round(((CurrencyItem)f1.Value).Rate / ((CurrencyItem)f0.Value).Rate, 4);
                f2.Info = "Rate from Yahoo finance at " + (f0.Value.Time > f1.Value.Time ? f1.Value.ToDateTimeString() : f0.Value.ToDateTimeString());
            };
            ((ComboBox)f0.Control).SelectedIndexChanged += indexChanged;
            ((ComboBox)f1.Control).SelectedIndexChanged += indexChanged;

            new InputBox(_excelapp, "To Currency", f0, f1, f2, f3, f4).Show(delegate {
                int lcid = ((CurrencyItem)f0.Value).LCID;
                char decimalSeparator = ((CurrencyItem)f0.Value).DecimalCharacter;
                decimal rate = (decimal)(f2.Value ?? 0);
                int precision = (int)(f3.Value ?? -1);
                string format = f4.Value ? ((CurrencyItem)f1.Value).Format : null;
                new CellsOperator(_range).ForEachValue(format, delegate(ref object value) {
                    decimal number;
                    if (Parser.ToCurrency(value, decimalSeparator, out number)) {
                        if (precision == -1)
                            value = number * rate;
                        else
                            value = Math.Round(number * rate, precision);
                    }
                });
            });
        }

        protected CurrencyItem[] ParseCurrencies(XmlDocument xmldoc) {
            var currencies = new List<CurrencyItem>();
            //currencies.Add(_currenciesList["EUR"]);
            //foreach (XmlNode cube in xmldoc.DocumentElement.SelectNodes("./Cube/Cube/Cube")) {
            foreach (XmlNode node in xmldoc.DocumentElement.SelectNodes("./resources/resource")) {
                //var code = node.Attributes["currency"].Value;
                var code = node.SelectSingleNode("field[@name='symbol']").InnerText.Substring(0, 3);
                if (_currenciesList.ContainsKey(code)) {
                    var currency = _currenciesList[code];
                    //currency.Rate = double.Parse(node.Attributes["rate"].Value);
                    currency.Rate = double.Parse(node.SelectSingleNode("field[@name='price']").InnerText);
                    currency.Time = DateTime.ParseExact(node.SelectSingleNode("field[@name='utctime']").InnerText.Substring(0, 19), "s", CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal);
                    currencies.Add(currency);
                }
            }
            currencies.Sort((CurrencyItem p1, CurrencyItem p2) => string.Compare(p1.Code, p2.Code, true));
            return currencies.ToArray();
        }

        protected XmlDocument LoadXmlFile(string xmlUrl) {
            var xmlPath = Settings.ApplicationDataDir + "\\currency.xml";
            var xmldoc = new XmlDocumentNoNS();
            try {
                if (File.Exists(xmlPath) == false || (DateTime.Now - File.GetLastWriteTime(xmlPath)).TotalHours > 1) {
                    xmldoc.Load(xmlUrl);
                    xmldoc.Save(xmlPath);
                    return xmldoc;
                }
            } catch (Exception) {
                var msg = "Failed to update conversion rates from the web.\r\nDo you want to update rates manually?";
                if (MsgBox.ShowWarn(_excelapp, msg, MessageBoxButtons.YesNo) == DialogResult.Yes && File.Exists(xmlPath))
                    System.Diagnostics.Process.Start("notepad.exe", xmlPath);
                return null;
            }
            xmldoc.Load(xmlPath);
            return xmldoc;
        }

        protected int GetCellCurrencyIndex(Excel.Range cell, CurrencyItem[] currencies) {
            int index = -1;
            string firstFormat = (string)cell.NumberFormat;
            //Search a match on lcid
            var lcidMatch = Regex.Match(firstFormat, @"\[\$[^-]+-([0-9A-F]+)\]|").Groups[1];
            if (lcidMatch.Success) {
                int lcid = int.Parse(lcidMatch.Value, NumberStyles.HexNumber);
                index = currencies.Length;
                while (--index != -1 && currencies[index].LCID != lcid) { }
                if (index != -1)
                    return index;
            }
            //Search a match on code
            var codeMath = Regex.Match(firstFormat, @"\[\$([A-Z]+)\]").Groups[1];
            if (codeMath.Success) {
                string code = codeMath.Value;
                index = currencies.Length;
                while (--index != -1 && currencies[index].Code != code) { }
                if (index != -1)
                    return index;
            }
            //Search a match on symbole
            string firstText = (string)cell.Text;
            var symbole = Regex.Match(firstText, @"^[^\d\+-]+|[^\d\+-]+$").Value.Trim();
            if (symbole != string.Empty) {
                index = currencies.Length;
                while (--index != -1 && currencies[index].Symbole != symbole) { }
            }
            return index;
        }

        //List currency cultures
        //static void Main(string[] args) {
        //    var cultures = new List<Tuple<CultureInfo, RegionInfo>>();
        //    var ids = new List<string>();
        //    foreach (CultureInfo culture in CultureInfo.GetCultures(CultureTypes.AllCultures)) {
        //        try {
        //            var region = new RegionInfo(culture.LCID);
        //            cultures.Add(new Tuple<CultureInfo, RegionInfo>(culture, new RegionInfo(culture.LCID)));
        //            if (ids.Contains(region.ISOCurrencySymbol)) continue;
        //            ids.Add(region.ISOCurrencySymbol);
        //        } catch { }
        //    }
        //    cultures.Sort((x, y) => x.Item1.LCID.CompareTo(y.Item1.LCID));
        //    ids.Sort();

        //    string format = "{{ \"{0}\", new Item(\"{0}\", \"{1}\", \"{2}\", {3}, '{4}', {5} ) }},";
        //    foreach (var id in ids) {
        //        var firstCulture = cultures.First(item => item.Item2.ISOCurrencySymbol == id);
        //        var lastCulture = cultures.Last(item => item.Item2.ISOCurrencySymbol == id);
        //        if (id == "EUR")
        //            System.Diagnostics.Debug.WriteLine(string.Format(format, "EUR", "Europe", '€', 0x1, ',', 3));
        //        else
        //            System.Diagnostics.Debug.WriteLine(string.Format(format,
        //                lastCulture.Item2.ISOCurrencySymbol,
        //                lastCulture.Item2.EnglishName,
        //                lastCulture.Item2.CurrencySymbol,
        //                "0x" + firstCulture.Item1.LCID.ToString("X"),
        //                lastCulture.Item1.NumberFormat.CurrencyDecimalSeparator == "," ? ',' : '.',
        //                lastCulture.Item1.NumberFormat.CurrencyPositivePattern.ToString()
        //            ));
        //    }
        //}

    }
}
